package com.huaxia.dao.product;

import com.huaxia.pojo.product.BranchEvergreen;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/10 14:14
 */

public interface BranchEvergreenMapper {


    @Select("select a.BRANCHSNAME,a.branchAllAgent,b.branchPreNum,c.branchEvergreenNum,round(decode(b.branchPreNum,0,0,c.branchEvergreenNum*100/b.branchPreNum),1) preEvergreenYield,d.branchEvergreen,e.branchMonSure,\n" +
            "round(decode(e.branchMonSure,0,0,d.branchEvergreen*100/e.branchMonSure),1)branchEvergreenYield from\n" +
            "(select SB.BRANCHSNAME,NVL(a.branchAllAgent,0)branchAllAgent from\n" +
            "(select t.BRANCHCODE,count(t.BRANCHCODE) branchAllAgent from D_AGENT_PROVINCE_YX2 t where  AGENTSTATE='在职' and t.name <> '客户服务专员' group by t.BRANCHCODE)a right join SD_BRANCH sb on a.BRANCHCODE=sb.BRANCHCODE\n" +
            ")a,\n" +
            "(select sb.branchsname,NVL(a.num,0)branchPreNum from\n" +
            "(select a.branchcode,count(a.agentcode)num from\n" +
            "(select t.BRANCHCODE, t.AGENTCODE,SUM(t.WRITTENSTADPREM)cb from FACT_YX_PREPREM_LIST_day t\n" +
            "            where to_char(t.issue_date,'yyyy/mm')=to_char(sysdate,'yyyy/mm') and t.agentstate='在职' group by t.agentcode,t.branchcode)a where a.cb>0 group by a.branchcode)a\n" +
            "            right join SD_BRANCH sb on sb.branchcode=a.branchcode\n" +
            ")b,\n" +
            "(select sb.branchsname,NVL(a.num,0)branchEvergreenNum  from(                      \n" +
            "select a.branchcode,count(a.branchcode)num from(select t.branchcode from FACT_YX_PREPREM_LIST_day t\n" +
            "where to_char(t.issue_date,'yyyy/mm')=to_char(sysdate,'yyyy/mm') and t.agentstate='在职' and  instr(t.PRODUCT_NAME,'常青树')>0 group by t.agentcode,t.branchcode)a group by a.branchcode)a right join SD_BRANCH sb on sb.branchcode=a.branchcode\n" +
            ")c,\n" +
            "(select sb.branchsname,NVL(a.ycb,0)branchEvergreen from           \n" +
            "(select a.BRANCHCODE,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "            (select t.BRANCHCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t \n" +
            "            where to_char(t.ISSUE_DATE,'yyyy/mm')  = to_char(sysdate,'yyyy/mm') and  instr(t.PRODUCT_NAME,'常青树')>0\n" +
            "             and t.RNFLAG='N'  group by t.BRANCHCODE)a left join \n" +
            "            (select s.BRANCHCODE,SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s \n" +
            "            where  to_char(s.yt_date,'yyyy/mm')  = to_char(sysdate,'yyyy/mm') and instr(s.PRODUCT_NAME,'常青树')>0 and s.RNFLAG='N'\n" +
            "            group by s.BRANCHCODE)b on a.BRANCHCODE=b.BRANCHCODE\n" +
            ")a right join SD_BRANCH sb on sb.branchcode=a.branchcode\n" +
            ")d,\n" +
            "(select sb.branchsname,NVL(a.ycb,0)branchMonSure from           \n" +
            "(select a.BRANCHCODE,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
            "            (select t.BRANCHCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t \n" +
            "            where to_char(t.ISSUE_DATE,'yyyy/mm')  = to_char(sysdate,'yyyy/mm') \n" +
            "             and t.RNFLAG='N'  group by t.BRANCHCODE)a left join \n" +
            "            (select s.BRANCHCODE,SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s \n" +
            "            where  to_char(s.yt_date,'yyyy/mm')  = to_char(sysdate,'yyyy/mm') and s.RNFLAG='N'\n" +
            "            group by s.BRANCHCODE)b on a.BRANCHCODE=b.BRANCHCODE\n" +
            ")a right join SD_BRANCH sb on sb.branchcode=a.branchcode\n" +
            ")e where a.BRANCHSNAME=b.BRANCHSNAME and b.BRANCHSNAME=c.BRANCHSNAME and c.BRANCHSNAME=d.BRANCHSNAME and d.BRANCHSNAME=e.BRANCHSNAME order by c.branchEvergreenNum DESC")
    List<BranchEvergreen> getBranchEvergreen();

}
